package com.example.student.service.impl;
import com.example.student.mapper.ScoreExcelMapper;
import com.example.student.pojo.Score;
import com.example.student.service.ScoreExcelService;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

/**
 * @author 34082
 */
@Slf4j
@Service
public class ScoreExcelServiceImpl implements ScoreExcelService {

    private ScoreExcelMapper excelMapper;
    @Autowired
    public void setExcelMapper(ScoreExcelMapper excelMapper) {
        this.excelMapper = excelMapper;
    }

    @Override
    public void createAllReport() {
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("成绩表");

            // 创建表头
            Row headerRow = sheet.createRow(0);
            String[] headers = {"学号", "姓名", "语文成绩", "语文专业平均分", "数学成绩", "数学专业平均分", "英语成绩", "英语专业平均分", "Java成绩", "Java专业平均分", "体育成绩", "体育专业平均分", "学生总成绩", "专业总成绩平均分"};
            for (int i = 0; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
            }

            List<Score> scores = excelMapper.findAllStudents();

            // 计算班级各科平均分
            double chineseAvg = scores.stream().mapToDouble(Score::getChinese).average().orElse(0.0);
            double mathAvg = scores.stream().mapToDouble(Score::getMath).average().orElse(0.0);
            double englishAvg = scores.stream().mapToDouble(Score::getEnglish).average().orElse(0.0);
            double javaAvg = scores.stream().mapToDouble(Score::getJava).average().orElse(0.0);
            double sportAvg = scores.stream().mapToDouble(Score::getSports).average().orElse(0.0);
            double totalAvg = scores.stream().mapToDouble(Score::getTotal).average().orElse(0.0);

            // 插入数据
            int rowNum = 1;
            for (Score score : scores) {
                Row row = sheet.createRow(rowNum++);
                int cellNum = 0;

                // 插入学生信息
                row.createCell(cellNum++).setCellValue(score.getStudentNumber());
                row.createCell(cellNum++).setCellValue(score.getName());

                // 插入学生各科成绩
                row.createCell(cellNum++).setCellValue(score.getChinese());
                row.createCell(cellNum++).setCellValue(chineseAvg);
                row.createCell(cellNum++).setCellValue(score.getMath());
                row.createCell(cellNum++).setCellValue(mathAvg);
                row.createCell(cellNum++).setCellValue(score.getEnglish());
                row.createCell(cellNum++).setCellValue(englishAvg);
                row.createCell(cellNum++).setCellValue(score.getJava());
                row.createCell(cellNum++).setCellValue(javaAvg);
                row.createCell(cellNum++).setCellValue(score.getSports());
                row.createCell(cellNum++).setCellValue(sportAvg);

                // 插入学生总成绩
                row.createCell(cellNum++).setCellValue(score.getTotal());

                // 插入班级总成绩平均分
                row.createCell(cellNum).setCellValue(totalAvg);
            }

            // 对数据进行排序，按照总成绩降序
            sheet.setAutoFilter(new CellRangeAddress(0, sheet.getLastRowNum(), 0, headers.length - 1));
            sheet.createFreezePane(0, 1, 0, 1);
            sheet.setDisplayGridlines(true);

            // 保存文件
            try (FileOutputStream outputStream = new FileOutputStream("成绩表.xlsx")) {
                workbook.write(outputStream);
            } catch (IOException e) {
                log.info("Error generating: {}", e.getMessage());
            }
        } catch (IOException e) {
            log.info("Error generating : {}", e.getMessage());
        }
    }




    @Override
    public void createReportByClass(Integer classId) {

        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet(classId.toString()+"成绩单");
            // 创建表头
            Row headerRow = sheet.createRow(0);
            String[] headers = {"学号", "姓名", "语文成绩", "语文专业平均分", "数学成绩", "数学专业平均分", "英语成绩", "英语专业平均分", "Java成绩", "Java专业平均分", "体育成绩", "体育专业平均分", "学生总成绩", "专业总成绩平均分"};
            for (int i = 0; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
            }

            List<Score> scores = excelMapper.getScoresByClassId(classId);

            // 计算班级各科平均分
            double chineseAvg = scores.stream().mapToDouble(Score::getChinese).average().orElse(0.0);
            double mathAvg = scores.stream().mapToDouble(Score::getMath).average().orElse(0.0);
            double englishAvg = scores.stream().mapToDouble(Score::getEnglish).average().orElse(0.0);
            double javaAvg = scores.stream().mapToDouble(Score::getJava).average().orElse(0.0);
            double sportAvg = scores.stream().mapToDouble(Score::getSports).average().orElse(0.0);
            double totalAvg = scores.stream().mapToDouble(Score::getTotal).average().orElse(0.0);

            // 插入数据
            int rowNum = 1;
            for (Score score : scores) {
                Row row = sheet.createRow(rowNum++);
                int cellNum = 0;

                // 插入学生信息
                row.createCell(cellNum++).setCellValue(score.getStudentNumber());
                row.createCell(cellNum++).setCellValue(score.getName());

                // 插入学生各科成绩
                row.createCell(cellNum++).setCellValue(score.getChinese());
                row.createCell(cellNum++).setCellValue(chineseAvg);
                row.createCell(cellNum++).setCellValue(score.getMath());
                row.createCell(cellNum++).setCellValue(mathAvg);
                row.createCell(cellNum++).setCellValue(score.getEnglish());
                row.createCell(cellNum++).setCellValue(englishAvg);
                row.createCell(cellNum++).setCellValue(score.getJava());
                row.createCell(cellNum++).setCellValue(javaAvg);
                row.createCell(cellNum++).setCellValue(score.getSports());
                row.createCell(cellNum++).setCellValue(sportAvg);

                // 插入学生总成绩
                row.createCell(cellNum++).setCellValue(score.getTotal());

                // 插入班级总成绩平均分
                row.createCell(cellNum).setCellValue(totalAvg);
            }

            // 对数据进行排序，按照总成绩降序
            sheet.setAutoFilter(new CellRangeAddress(0, sheet.getLastRowNum(), 0, headers.length - 1));
            sheet.createFreezePane(0, 1, 0, 1);
            sheet.setDisplayGridlines(true);

            // 保存文件
            try (FileOutputStream outputStream = new FileOutputStream(classId+"成绩单.xlsx")) {
                workbook.write(outputStream);
            } catch (IOException e) {

                log.info("IOError generating : {}", e.getMessage());
            }
        } catch (IOException e) {
            log.info("IoError generating : {}", e.getMessage());
        }


    }


}
